/**
 * 检查孤立副将数据脚本
 * 快速检查当前数据库中是否有孤立的副将数据
 */

const mysql = require('mysql2/promise');

// 数据库配置
const DB_CONFIG = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4',
    timezone: '+08:00'
};

async function checkOrphanedDeputies() {
    let connection;

    try {
        console.log('🔗 连接数据库...');
        connection = await mysql.createConnection(DB_CONFIG);
        console.log('✅ 数据库连接成功');

        // 1. 检查副将表的基本统计
        console.log('\n📊 副将表统计:');
        const [deputyStats] = await connection.execute(`
            SELECT 
                COUNT(*) as total_deputies,
                COUNT(DISTINCT base_id) as unique_base_types,
                MIN(created_at) as earliest_deputy,
                MAX(created_at) as latest_deputy
            FROM deputy_general
        `);

        console.log(`   总副将数: ${deputyStats[0].total_deputies}`);
        console.log(`   基础类型数: ${deputyStats[0].unique_base_types}`);
        console.log(`   最早副将: ${deputyStats[0].earliest_deputy}`);
        console.log(`   最新副将: ${deputyStats[0].latest_deputy}`);

        // 2. 检查玩家副将表统计
        console.log('\n👥 玩家副将表统计:');
        const [playerDeputyStats] = await connection.execute(`
            SELECT 
                COUNT(*) as total_records,
                COUNT(DISTINCT deputy_id) as unique_deputies
            FROM player_deputy
        `);

        console.log(`   总记录数: ${playerDeputyStats[0].total_records}`);
        console.log(`   唯一副将数: ${playerDeputyStats[0].unique_deputies}`);

        // 3. 检查仓库表统计
        console.log('\n🏪 仓库表统计:');
        const [warehouseStats] = await connection.execute(`
            SELECT 
                COUNT(*) as total_records,
                COUNT(DISTINCT deputy_id) as unique_deputies
            FROM player_deputy_warehouse
        `);

        console.log(`   总记录数: ${warehouseStats[0].total_records}`);
        console.log(`   唯一副将数: ${warehouseStats[0].unique_deputies}`);

        // 4. 查找孤立的副将
        console.log('\n🔍 查找孤立的副将:');
        const [orphanedDeputies] = await connection.execute(`
            SELECT 
                dg.id,
                dg.base_id,
                dg.lvl,
                dg.star,
                dg.roleType,
                dg.created_at,
                dgb.name as deputy_name,
                dgb.star_level
            FROM deputy_general dg
            LEFT JOIN deputy_general_base dgb ON dg.base_id = dgb.id
            WHERE dg.id NOT IN (
                SELECT DISTINCT deputy_id FROM player_deputy WHERE deputy_id IS NOT NULL
                UNION
                SELECT DISTINCT deputy_id FROM player_deputy_warehouse WHERE deputy_id IS NOT NULL
            )
            ORDER BY dg.created_at ASC
        `);

        if (orphanedDeputies.length === 0) {
            console.log('   ✅ 没有发现孤立的副将，数据正常');
        } else {
            console.log(`   ❌ 发现 ${orphanedDeputies.length} 个孤立的副将:`);
            orphanedDeputies.forEach((deputy, index) => {
                console.log(`      ${index + 1}. ID: ${deputy.id}, 名称: ${deputy.deputy_name || '未知'}, 等级: ${deputy.lvl}, 星级: ${deputy.star}, 创建时间: ${deputy.created_at}`);
            });
        }

        // 5. 检查孤立副将的关联数据
        if (orphanedDeputies.length > 0) {
            console.log('\n📊 孤立副将的关联数据统计:');

            for (const deputy of orphanedDeputies) {
                const [skills] = await connection.execute(
                    'SELECT COUNT(*) as count FROM deputy_general_skill_learned WHERE deputy_id = ?',
                    [deputy.id]
                );

                const [equipment] = await connection.execute(
                    'SELECT COUNT(*) as count FROM deputy_general_equipment WHERE deputy_id = ?',
                    [deputy.id]
                );

                const [stones] = await connection.execute(
                    'SELECT COUNT(*) as count FROM deputy_general_stone WHERE deputy_id = ?',
                    [deputy.id]
                );

                console.log(`   副将 ${deputy.id} (${deputy.deputy_name || '未知'}): 技能${skills[0].count}个, 装备${equipment[0].count}个, 宝石${stones[0].count}个`);
            }
        }

        // 6. 数据一致性检查
        console.log('\n🔍 数据一致性检查:');

        // 检查是否有副将在两个表中都存在
        const [duplicates] = await connection.execute(`
            SELECT 
                pd.player_id,
                pd.deputy_id,
                pd.created_at as player_deputy_created,
                pw.created_at as warehouse_created
            FROM player_deputy pd
            INNER JOIN player_deputy_warehouse pw 
                ON pd.player_id = pw.player_id 
                AND pd.deputy_id = pw.deputy_id
        `);

        if (duplicates.length === 0) {
            console.log('   ✅ 没有发现重复的副将记录');
        } else {
            console.log(`   ❌ 发现 ${duplicates.length} 个重复的副将记录:`);
            duplicates.forEach(dup => {
                console.log(`      玩家ID: ${dup.player_id}, 副将ID: ${dup.deputy_id}`);
            });
        }

        // 7. 总结
        console.log('\n📋 总结:');
        const totalOrphaned = orphanedDeputies.length;
        const totalDeputies = deputyStats[0].total_deputies;
        const orphanedPercentage = totalDeputies > 0 ? (totalOrphaned / totalDeputies * 100).toFixed(2) : 0;

        console.log(`   总副将数: ${totalDeputies}`);
        console.log(`   孤立副将数: ${totalOrphaned}`);
        console.log(`   孤立比例: ${orphanedPercentage}%`);

        if (totalOrphaned > 0) {
            console.log('\n⚠️ 建议执行清理脚本: node cleanup-orphaned-deputies.js');
        } else {
            console.log('\n✅ 数据状态正常，无需清理');
        }

    } catch (error) {
        console.error('❌ 检查过程中发生错误:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('\n🔌 数据库连接已关闭');
        }
    }
}

// 运行检查
checkOrphanedDeputies().catch(console.error);
